package com.person.jc.service.base.notice;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.person.jc.service.Service;
import com.person.jc.utils.Utils;

public class BdNoticeService extends Service {
	
	public final static BdNoticeService me = new BdNoticeService();
	
	public List<Record> getList(Map<String, Object> map){
		List<Object> params = new ArrayList<Object>();
		String temp = "";
		StringBuffer sql = new StringBuffer("select *,date_format(notice_date, '%Y-%m-%d %H:%i:%s') notice_date_ from bd_notice where 1=1");
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("noticeId")))){
			sql.append(" and notice_id = ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("noticeTitle")))){
			sql.append(" and notice_title like ?");
			params.add("%"+temp+"%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and date_format(notice_date, '%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endtDate")))){
			sql.append(" and date_format(notice_date, '%Y-%m-%d') <= ?");
			params.add(temp);
		}		
		sql.append(" order by notice_date desc");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	
	public List<Record> getMyList(Map<String, Object> map) {
		List<Object> params = new ArrayList<Object>();
		String temp = "";
		StringBuffer sql = new StringBuffer("select n.*,date_format(n.notice_date, '%Y-%m-%d %H:%i:%s') notice_date_ FROM bd_notice_user u,bd_notice n WHERE u.notice_id=n.notice_id ");
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("noticeId")))){
			sql.append(" and n.notice_id = ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("user_id")))){
			sql.append(" and u.user_id = ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("noticeTitle")))){
			sql.append(" and n.notice_title like ?");
			params.add("%"+temp+"%");
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("startDate")))){
			sql.append(" and date_format(n.notice_date, '%Y-%m-%d') >= ?");
			params.add(temp);
		}
		if(Utils.isNotEmpty(temp = String.valueOf(map.get("endtDate")))){
			sql.append(" and date_format(n.notice_date, '%Y-%m-%d') <= ?");
			params.add(temp);
		}		
		sql.append(" order by n.notice_date desc");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}
	
	
	public String save(Map<String, Object> map){
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer("insert into bd_notice ( notice_title, notice_desc, notice_user, notice_date,notice_is_readed) values");
		sql.append("( ?, ?, ?, ?,0)");
		params.add(map.get("noticeTitle"));
		params.add(map.get("noticeDesc"));
		params.add(map.get("noticeUser"));
		params.add(map.get("noticeDate"));
		Db.update(sql.toString(), Utils.getObjParam(params));
		String idxSql = "SELECT LAST_INSERT_ID() from dual ";
		String noticeId = Db.queryColumn(idxSql).toString();
		super.setCRUD("通知公告", "C", "新增通知公告:"+noticeId);
		return noticeId;
	}
	
	public void update(Map<String, Object> map){
		List<Object> params = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer(" update bd_notice set notice_title = ?, notice_desc = ? ,");
		sql.append(" notice_user = ? , notice_date = ? where notice_id = ?");
		params.add(map.get("noticeTitle"));
		params.add(map.get("noticeDesc"));
		params.add(map.get("noticeUser"));
		params.add(map.get("noticeDate"));
		params.add(map.get("noticeId"));
		Db.update(sql.toString(), Utils.getObjParam(params));
		super.setCRUD("通知公告", "U", "修改通知公告:"+map.get("noticeId"));
	}
	
	public void saveUsers(String noticeId, String[] users){
		String sql = " insert into bd_notice_user (notice_id, user_id) values (?, ?)";
		if(users != null && users.length != 0){
			String[][] paras = new String[users.length][2];
			for (int i = 0; i < users.length; i++) {
				paras[i][0] = noticeId;
				paras[i][1] = users[i];
			}
			Db.batch(sql, paras, users.length);
			super.setCRUD("通知公告", "C", "新增通知人员:"+noticeId);
		}
	}
	
	public void removeUsers(String noticeId){
		String sql = "delete from bd_notice_user where notice_id = ?";
		Db.update(sql, noticeId);
		super.setCRUD("通知公告", "D", "删除通知人员:"+noticeId);
	}
	
	public void delete(String noticeId){
		String sql = "delete from bd_notice where notice_id = ?";
		Db.update(sql, noticeId);
		super.setCRUD("通知公告", "D", "删除通知公告:"+noticeId);
	}
	
	public List<Record> getUserList(String noticeId, String view){
		String temp  = "LEFT";
		if(Utils.isNotEmpty(view)){
			temp = "RIGHT";
		}
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT \n    t.user_id,\n    t.user_name,\n    t.dept_id,\n    d.dept_name,\n    t1.user_id flag\nFROM\n    ts_user t\n"
				+ "        LEFT JOIN\n    ts_dept d ON t.dept_id = d.dept_id\n"
				+ "        "+temp+" JOIN\n    bd_notice_user t1 ON t.user_id = t1.user_id\n "
				+ "       AND t1.notice_id = '" + noticeId + "'\n"
				+ "WHERE\n    t.status = 1 AND d.status = 1\n"
				+ "ORDER BY t.dept_id, t.user_id");
		
		return Db.find(sql.toString());
	}

	public List<Record> getMyNotice(String userId) {
		List<Object> params = new ArrayList<Object>();
		params.add(userId);
		StringBuffer sql = new StringBuffer("SELECT date_format(n.notice_date,'%Y-%m-%d %H:%i:%s') notice_date,n.notice_title FROM bd_notice_user u,bd_notice n WHERE u.notice_id=n.notice_id AND user_id = ? AND n.notice_is_readed=0");
		sql.append(" order by n.notice_date desc");
		return Db.find(sql.toString(), Utils.getObjParam(params));
	}


	public List<Record> getUsers() {
		StringBuffer sql = new StringBuffer("select user_id from ts_user ");
		return Db.find(sql.toString());
	}


}
